package org.jeecg.modules.online.cgform.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtil;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.system.api.ISysBaseAPI;
import org.jeecg.common.system.query.MatchTypeEnum;
import org.jeecg.common.system.util.JeecgDataAutorUtils;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecg.common.system.vo.SysPermissionDataRuleModel;
import org.jeecg.common.util.dynamic.db.DbTypeUtils;
import org.jeecg.common.util.oConvertUtils;
import org.jeecg.modules.online.auth.service.IOnlAuthDataService;
import org.jeecg.modules.online.cgform.constant.ExtendJsonKey;
import org.jeecg.modules.online.cgform.entity.OnlCgformField;
import org.jeecg.modules.online.cgform.entity.OnlCgformHead;
import org.jeecg.modules.online.cgform.mapper.OnlineMapper;
import org.jeecg.modules.online.cgform.model.OnlTable;
import org.jeecg.modules.online.cgform.model.SqlOrder;
import org.jeecg.modules.online.cgform.service.IOnlCgformFieldService;
import org.jeecg.modules.online.cgform.service.IOnlCgformHeadService;
import org.jeecg.modules.online.cgform.service.IOnlineJoinQueryService;
import org.jeecg.modules.online.cgform.util.CgformUtil;
import org.jeecg.modules.online.config.model.OnlineFieldConfig;
import org.jeecg.modules.online.config.util.ConditionHandler;
import org.jeecg.modules.online.config.util.DbTableUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * online主子表关联查询专用(单表查询也可以走)
 *
 * 列表列 规则： 重复的列 主表保持原样 子表：表名_字段名
 * 列表数据 规则：重复的列 主表字段保持原样 子表：as 表名_字段名 见方法：getSelectAliasFieldSql
 * 列表查询条件： 联合查询下 查询参数均为 表名@字段名
 *
 * 支持功能如下：
 * 主子表join查询
 * 主子表查询条件解析
 * 主子表数据权限解析
 * 主子表排序自定义配置
 * 主子表导出 子表数据筛选
 */
@Slf4j
@Service("onlineJoinQueryService")
public class OnlineJoinQueryServiceImpl implements IOnlineJoinQueryService {

    @Autowired
    IOnlCgformFieldService onlCgformFieldService;

    @Autowired
    IOnlCgformHeadService onlCgformHeadService;

    @Autowired
    private IOnlAuthDataService onlAuthDataService;

    @Autowired
    private ISysBaseAPI sysBaseAPI;

    @Autowired
    private OnlineMapper onlineMapper;

    @Override
    public Map<String, Object> pageList(OnlCgformHead head, Map<String, Object> params, boolean ignoreSelectSubField) {
        LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        // 获取所有表配置
        List<OnlTable> list = getJoinQueryTables(head, sysUser.getId());
        // 获取高级查询参数
        JSONArray superQueryParams = CgformUtil.getSuperQueryParams(params);
        // 获取高级查询条件规则 and/or
        MatchTypeEnum matchType = CgformUtil.getSuperQueryRule(params);
        // 高级查询sql
        StringBuilder builder = new StringBuilder();
        // 是不是第一次处理高级查询条件
        boolean isFirstSuperQuery = true;
        // 获取基础sql
        String mainTableAlias = "";

        boolean loadCacheUser = false;
        // 记录form join的sql
        StringBuffer fromJoinSql = new StringBuffer();
        // 记录查询条件where后面的sql
        StringBuffer conditionSql = new StringBuffer();
        // 记录查询字段 select后面的sql
        List<String> sqlFieldList = new ArrayList<>();
        // 排序
        List<SqlOrder> orderList = new ArrayList<SqlOrder>();
        // 记录字段-出现次数
        Map<String, Integer> fieldRecord = new HashMap<>();
        // 记录别名-表名
        Map<String, String> tableAliasMap = new HashMap<>();
        // 导出字段
        List<OnlCgformField> exportFieldList = new ArrayList<>();
        // 查询参数
        Map<String,Object> sqlParams = new HashMap<>();
        for(OnlTable table: list){

            List<OnlCgformField> selectFieldList = table.getSelectFieldList();
            String alias = table.getAlias();
            String fieldAlias = alias+".";
            String tableAlias = " " + alias + " ";
            String tableName = table.getTableName();

            // 数据权限加载
            List<OnlCgformField> allFieldList = table.getAllFieldList();
            List<SysPermissionDataRuleModel> authList = table.getAuthList();
            if(!loadCacheUser && authList!=null && authList.size()>0){
                JeecgDataAutorUtils.installUserInfo(sysBaseAPI.getCacheUser(sysUser.getUsername()));
                loadCacheUser = true;
            }
            // 获取查询条件 and a = xx and b = xxxx
           // String condition = CgformUtil.getAutoListConditionSql(allFieldList, params, null, authList, fieldAlias, tableName+"@");

            ConditionHandler handler = new ConditionHandler(fieldAlias);
            handler.setNeedList(null);
            handler.setFirst(false);
            List<OnlineFieldConfig> fieldConfigs = CgformUtil.getOnlineFieldList(allFieldList);
            String tempConditionSql = handler.getConditionSql(fieldConfigs, params, authList, tableName+"@");
            Map<String,Object> sqlParam = handler.getSqlParams();
            sqlParams.putAll(sqlParam);

            // 处理order by语句 order by a.id desc, b.name asc, ....
            boolean hasOrderBy = handleOrderBySql(params, table.isMain(), tableName, fieldAlias, allFieldList, orderList);
            // 是否有查询字段
            boolean hasSelectField = hasSelectField(table);
            // 是否有高级查询条件
            boolean hasSuperQueryParam = hasSuperQueryParam(table, superQueryParams);
            // 是否有查询条件
            boolean hasSearch = tempConditionSql.length()>0;

            boolean isJoinTable = hasSelectField || hasSuperQueryParam || hasSearch || hasOrderBy;
            if(isJoinTable==false){
                // 如果子表没有select字段 也没有where条件 也没有order by 不需要join
                continue;
            }

            // 是否是 EXISTS 语句
            boolean isExistSql = hasSelectField==false && (hasSuperQueryParam||hasSearch);
            if(hasOrderBy){
                // order by有一票否决权  有 order by必须join查询
                isExistSql = false;
            }

            // 导出的时候ignoreSelectSubField传true  不查子表字段
            // 当ignoreSelectSubField为true则是导出逻辑 那么只查主表字段  当为false时，需要根据实际是否有查询字段判断
            if((ignoreSelectSubField && table.isMain()) || (!ignoreSelectSubField && hasSelectField)){
                // 获取查询的字段select a.A1,a.A2,...a.An,b.B1,b.B2,...b.Bn
                handleSelectFieldSql(fieldAlias, table.isMain(), selectFieldList, sqlFieldList, fieldRecord);
            }

            // 获取导出的主表字段
            if(ignoreSelectSubField && table.isMain()){
                exportFieldList = selectFieldList;
            }

            // 获取高级查询的sql语句
            String superQuerySql = "";
            ConditionHandler superQueryConditionHandler = handleSingleTableSuperQuery(table, superQueryParams, matchType.getValue(), handler);
            if(superQueryConditionHandler!=null){
                superQuerySql = superQueryConditionHandler.getSql().toString();
                if(superQuerySql.length()>0){
                    sqlParams.putAll(superQueryConditionHandler.getSqlParams());
                }
            }
            // 获取from join的sql  from table a join b on a.xx = b.xxx
            if(table.isMain()){
                fromJoinSql.append(CgformUtil.SQL_FROM + CgformUtil.getRealTableName(tableName) + tableAlias);
                mainTableAlias = fieldAlias;
            }else{
                // 子表 别名和表名 对应关系记录
                tableAliasMap.put(alias, tableName);
                //判断是join逻辑还是 EXISTS 逻辑
                if(isExistSql){
                    String existsSql = getExistsSql(table, mainTableAlias, tempConditionSql, superQuerySql);
                    conditionSql.append(existsSql);
                }else{
                    fromJoinSql.append(" LEFT JOIN ");
                    fromJoinSql.append(CgformUtil.getRealTableName(tableName));
                    fromJoinSql.append(tableAlias);
                    fromJoinSql.append(CgformUtil.SQL_ON);
                    fromJoinSql.append(fieldAlias);
                    fromJoinSql.append(table.getJoinField());
                    fromJoinSql.append(CgformUtil.SQL_EQ);
                    fromJoinSql.append(mainTableAlias);
                    fromJoinSql.append(table.getMainField());
                }
            }

            if(!isExistSql){
                // 查询条件
                conditionSql.append(tempConditionSql);
                // 处理高级查询
                if(superQuerySql.length()>0){
                    if(isFirstSuperQuery){
                        builder.append(superQuerySql);
                        isFirstSuperQuery = false;
                    }else{
                        builder.append(" ").append(matchType.getValue()).append(" ").append(superQuerySql);
                    }
                }
                //isFirstSuperQuery = handleSuperQueryJoinSql(builder, superQueryParams, matchType, fieldAlias, tableName, allFieldList, table.isMain(), isFirstSuperQuery);
            }
        }
        String selectFieldSql = getSelectAliasFieldSql(sqlFieldList, fieldRecord, tableAliasMap);
        String superQuerySql = getSuperQuerySql(builder);
        String orderBySql = getOrderBySql(orderList);

        // select + 字段 + from xxx join xxxx + where 1 = 1+ and condition + and superQuery + order by
        String finalSql = CgformUtil.SQL_SELECT + selectFieldSql + fromJoinSql.toString()+ CgformUtil.SQL_WHERE_TRUE + conditionSql.toString() + superQuerySql;
        // 获取当前数据源类型
        DbType dbType = DbTableUtil.getDbType(null);
        if(!DbTypeUtils.dbTypeIsSQLServer(dbType)){
            // 不是 sqlserver 数据库的时候 需要增加排序设置
            finalSql+= orderBySql;
        }
        //log.info("---Online联合查询sql :>> " + finalSql);
        //log.info("---Online联合查询sqlParams :>> " + sqlParams);

        Map<String, Object> result = new HashMap<>();
        //分页查询
        Integer pageSize = params.get("pageSize")==null?10:Integer.parseInt(params.get("pageSize").toString());
        if(pageSize==-521) {
            //20190521 for:如果传此参数为-521 则表示不需要分页 直接走简单查询即可
           // List<Map<String,Object>> dataList =  this.onlCgformFieldService.queryListBySql(finalSql);
            List<Map<String,Object>> dataList = this.onlineMapper.selectByCondition(finalSql, sqlParams);
            if(dataList==null || dataList.size()==0) {
                result.put("total",0);
            }else {
                result.put("total", dataList.size());
                if(ignoreSelectSubField){
                    dataList = handleDuplicateData(dataList);
                }
                result.put("records", CgformUtil.toLowerCasePageList(dataList, tableAliasMap.values()));
            }
            if(ignoreSelectSubField){
                result.put("fieldList", exportFieldList);
            }
        }else {
            Integer pageNo = params.get("pageNo")==null?1:Integer.parseInt(params.get("pageNo").toString());
            Page<Map<String,Object>> page = new Page<>(pageNo, pageSize);
            //update-begin-author:taoyan date:20220104 for: JTC-414 【Online表单】联合查询，一对多子表数据多的情况下，分页异常
            //关闭mybatisplus sql查询count自动优化 设置成false 为：select count from (原来的sql)
            page.setOptimizeCountSql(false);
            //update-end-author:taoyan date:20220104 for: JTC-414 【Online表单】联合查询，一对多子表数据多的情况下，分页异常
            IPage<Map<String,Object>> pageList = this.onlineMapper.selectPageByCondition(page, finalSql, sqlParams);
            //IPage<Map<String,Object>> pageList = this.onlCgformFieldService.selectPageBySql(finalSql, pageNo, pageSize);
            //处理结果 返回需要的数据
            result.put("total", pageList.getTotal());
            List<Map<String,Object>> dataList = pageList.getRecords();
            if(ignoreSelectSubField){
                dataList = handleDuplicateData(dataList);
            }
            result.put("records", CgformUtil.toLowerCasePageList(dataList, tableAliasMap.values()));
        }
        return result;
    }

    /**
     *  获取 子表的exists查询语句
     * @param table
     * @param mainTableAlias
     * @param pageQuerySql
     * @param superQuerySql
     * @return
     */
    private String getExistsSql(OnlTable table, String mainTableAlias, String pageQuerySql, String superQuerySql){

        String alias = table.getAlias();
        String tableName = table.getTableName();

        String realTableName = CgformUtil.getRealTableName(tableName);
        String fieldAlias = alias+".";
        StringBuffer sb = new StringBuffer();
        sb.append(" AND EXISTS (");
        sb.append(CgformUtil.SQL_SELECT);
        sb.append(fieldAlias+CgformUtil.P_KEY);
        sb.append(CgformUtil.SQL_FROM);
        sb.append(realTableName);
        sb.append(" "+alias);
        sb.append(CgformUtil.SQL_WHERE);
        sb.append(fieldAlias);
        sb.append(table.getJoinField());
        sb.append(CgformUtil.SQL_EQ);
        sb.append(mainTableAlias);
        sb.append(table.getMainField());
        // 处理查询条件
        if(pageQuerySql!=null && pageQuerySql.length()>0){
           sb.append(pageQuerySql);
        }
        // 处理高级查询
        if(superQuerySql!=null && superQuerySql.length()>0){
            sb.append(" AND (").append(superQuerySql).append(") ");
        }
//        StringBuilder builder = new StringBuilder();
//        handleSuperQueryJoinSql(builder, superQueryParams, matchType, fieldAlias, tableName, allFieldList, false, true);
//        String superQuerySql = getSuperQuerySql(builder);
//        sb.append(superQuerySql);
        sb.append(")");
        return sb.toString();
    }

    @Override
    public Map<String, Object> pageList(OnlCgformHead head, Map<String, Object> params) {
        return pageList(head, params, false);
    }


    /**
     * 获取查询字段对应的sql  字段重复的情况会带别名
     * @param sqlFieldList select a.A1,a.A2,...a.An,b.B1,b.B2,...b.Bn
     * @param fieldRecord A1:1 A2:1 ... Bn:2
     * @param tableAliasMap a:tableName
     * @return
     */
    private String getSelectAliasFieldSql(List<String> sqlFieldList, Map<String, Integer> fieldRecord, Map<String, String> tableAliasMap){
        List<String> ls = new ArrayList<>();
        for(String sql: sqlFieldList){
            // 将a.A1 分割
            String[] arr = sql.split("\\.");
            String tableAlias = arr[0];
            if("a".equals(tableAlias)){
                //主表的不作别名
                ls.add(sql);
            }else{
                String fieldName = arr[1];
                int fieldNum = fieldRecord.get(fieldName);
                if(fieldNum>1){
                    String tableName = tableAliasMap.get(tableAlias);
                    ls.add(sql + " " + tableName + "_" + fieldName);
                }else{
                    ls.add(sql);
                }
            }
        }
        return String.join("," , ls);
    }

    /**
     * 获取查询的字段
     */
    private void handleSelectFieldSql(String fieldAlias, boolean isMain, List<OnlCgformField> selectFieldList, List<String> sqlFieldList, Map<String, Integer> fieldRecord){
        if(selectFieldList==null || selectFieldList.size()==0){
            if(isMain){
                sqlFieldList.add(fieldAlias + CgformUtil.P_KEY);
            }
        }else{
            // JOIN table b on b.xx = a.xxx
            int size = selectFieldList.size();
            for(int i=0;i<size;i++) {
                OnlCgformField item = selectFieldList.get(i);
                String fieldName = item.getDbFieldName();
                if(CgformUtil.P_KEY.equals(fieldName)) {
                    continue;
                }
                //排除不显示的列
                if(CgformUtil.SHOW_LIST_YES!=item.getIsShowList()){
                    continue;
                }
                //update-begin--Author:scott  Date:20190922 for：Online配置字典分类树字典报错，逻辑未处理字段文本为空情况-------------------
                if(CgformUtil.CAT_TREE.equals(item.getFieldShowType()) && StringUtil.isNotEmpty(item.getDictText())){
                    sqlFieldList.add(fieldAlias + item.getDictText());
                }
                //update-end--Author:scott  Date:20190922 for：Online配置字典分类树字典报错，逻辑未处理字段文本为空情况-------------------
                sqlFieldList.add(fieldAlias + fieldName);

                Integer fieldNum = fieldRecord.get(fieldName);
                if(fieldNum==null){
                    fieldRecord.put(fieldName, 1);
                }else{
                    fieldRecord.put(fieldName, 1+fieldNum);
                }
            }
            sqlFieldList.add(fieldAlias + CgformUtil.P_KEY);
            fieldRecord.put(CgformUtil.P_KEY, 2);
        }
    }

    /**
     * 查询数据 获取单个表的配置
     * @param head
     * @return
     */
    private OnlTable getOnlTable(OnlCgformHead head, int index, boolean isMain){
        String headId = head.getId();
        String tableName = head.getTableName();
        OnlTable onlTable = new OnlTable(tableName, headId, isMain);
        List<OnlCgformField> allFieldList = getFieldList(headId);
        List<OnlCgformField> selectFieldList = onlCgformFieldService.queryAvailableFields(headId, tableName, true, allFieldList, null);
        onlTable.setAllFieldList(allFieldList);
        onlTable.setSelectFieldList(selectFieldList);
        onlTable.setAliasByIntValue(index);
        if(isMain==false){
            // 如果是子表需要找 关联字段
            for(OnlCgformField item: allFieldList){
                if(StringUtil.isNotEmpty(item.getMainField()) && StringUtil.isNotEmpty(item.getMainTable())){
                    onlTable.setMainField(item.getMainField());
                    onlTable.setJoinField(item.getDbFieldName());
                    break;
                }
            }
        }
        return onlTable;
    }

    /**
     * 查询数据 获取所有表的配置
     * @param head
     * @return
     */
    private List<OnlTable> getJoinQueryTables(OnlCgformHead head, String userId){
        // 别名从字母a开始
        int index = (int)'a';
        List<OnlTable> list = new ArrayList<>();
        OnlTable mainTable = getOnlTable(head, index++, true);
        List<SysPermissionDataRuleModel> mainTableAuthList = onlAuthDataService.queryUserOnlineAuthData(userId, head.getId());
        mainTable.setAuthList(mainTableAuthList);
        list.add(mainTable);

        Integer tableType = head.getTableType();
        if(tableType!=null && tableType==2){
            String subTableStr = head.getSubTableStr();
            if(subTableStr!=null && !"".equals(subTableStr)){
                String[] arr = subTableStr.split(",");
                for(String subTableName: arr){
                    OnlCgformHead subTable = onlCgformHeadService.getOne(new LambdaQueryWrapper<OnlCgformHead>().eq(OnlCgformHead::getTableName, subTableName));
                    if(subTable==null){
                        continue;
                    }
                    OnlTable onlTable = getOnlTable(subTable, index++, false);
                    List<SysPermissionDataRuleModel> authList = onlAuthDataService.queryUserOnlineAuthData(userId, subTable.getId());
                    onlTable.setAuthList(authList);
                    list.add(onlTable);
                }
            }
        }

        return list;
    }

    /**
     * 获取关联查询相关的所有字段 返回 一个map  key-表名  value-表对应的字段
     * @return
     */
    private Map<String, List<OnlCgformField>> getJoinQueryFields(OnlCgformHead head, Map<String, String> tableNameAndId){
        Map<String, List<OnlCgformField>> map = new HashMap<>();
        // 主表
        tableNameAndId.put(head.getTableName(), head.getId());
        List<OnlCgformField> allFieldList = getFieldList(head.getId());
        map.put(head.getTableName(), allFieldList);

        Integer tableType = head.getTableType();
        if(tableType!=null && tableType==2){
            String subTableStr = head.getSubTableStr();
            if(subTableStr!=null && !"".equals(subTableStr)){
                String[] arr = subTableStr.split(",");
                for(String subTableName: arr){
                    OnlCgformHead subTable = onlCgformHeadService.getOne(new LambdaQueryWrapper<OnlCgformHead>().eq(OnlCgformHead::getTableName, subTableName));
                    if(subTable==null){
                        continue;
                    }

                    // 子表
                    tableNameAndId.put(subTable.getTableName(), subTable.getId());
                    List<OnlCgformField> subFieldList = getFieldList(subTable.getId());
                    map.put(subTable.getTableName(), subFieldList);
                }
            }
        }
        return map;
    }

    /**
     * 查询 表配置对应的字段集合
     * @param id head表的id
     * @return
     */
    private List<OnlCgformField> getFieldList(String id){
        LambdaQueryWrapper<OnlCgformField> query = new LambdaQueryWrapper<OnlCgformField>();
        query.eq(OnlCgformField::getCgformHeadId, id);
        query.orderByAsc(OnlCgformField::getOrderNum);
        return this.onlCgformFieldService.list(query);
    }

    /**
     * 获取排序的列
     * @param params
     * @param isMain
     * @param tableName
     * @param alias
     * @param allFieldList
     * @param orderList
     */
    private boolean handleOrderBySql(Map<String,Object> params, boolean isMain, String tableName, String alias, List<OnlCgformField> allFieldList, List<SqlOrder> orderList){
        boolean hasOrderBy = isMain?true:false;
        // 1.判断前端是否传过来排序 如果有那么使用该值排序
        Object columnParam = params.get("column");
        if(columnParam!=null && !"id".equals(columnParam.toString())){
            String column = columnParam.toString();
            Object ruleObj = params.get("order");
            String rule = SqlOrder.DESC;
            if(ruleObj!=null){
                rule = ruleObj.toString();
            }
            if(isMain){
                // 主表直接拿到参数 和原字段集合匹配 如果有 就说明是
                if(CgformUtil.hasField(column, allFieldList)){
                    SqlOrder sqlOrder = new SqlOrder(column, rule);
                    sqlOrder.setAlias(alias);
                    orderList.add(sqlOrder);
                }
            }else{
                if(column.startsWith(tableName)){
                    String realColumn = column.replaceFirst(tableName+"_", "");
                    if(CgformUtil.hasField(realColumn, allFieldList)){
                        SqlOrder sqlOrder = new SqlOrder(realColumn, rule);
                        sqlOrder.setAlias(alias);
                        orderList.add(sqlOrder);
                        hasOrderBy = true;
                    }
                }
            }
        }else{
            // 2.前端没有传排序字段 用配置的排序字段
            for(OnlCgformField field: allFieldList){
                if("1".equals(field.getSortFlag())){
                    String extJson = field.getFieldExtendJson();
                    SqlOrder sqlOrder = new SqlOrder(field.getDbFieldName());
                    sqlOrder.setAlias(alias);
                    if(extJson!=null && !"".equals(extJson)){
                        JSONObject json = JSON.parseObject(extJson);
                        String orderRule = json.getString(ExtendJsonKey.ORDER_RULE);
                        if(orderRule!=null && !"".equals(orderRule)){
                            sqlOrder.setRule(orderRule);
                            // update-begin-author:taoyan date:20220119 for:JTC-489【online排序】如果用户不想修改默认的排序，但是设置了排序字段就会导致出问题
                            orderList.add(sqlOrder);
                            hasOrderBy = true;
                        }
                    }
                    // update-end-author:taoyan date:20220119 for:JTC-489【online排序】如果用户不想修改默认的排序，但是设置了排序字段就会导致出问题
                }
            }
        }
        return hasOrderBy;
    }

    /**
     * 获取排序的sql语句
     * @param ls
     * @return
     */
    private String getOrderBySql(List<SqlOrder> ls){
        // 如果没有配置排序字段 那么使用默认的ID排序
        if(ls.size()==0){
            SqlOrder sqlOrder = SqlOrder.createDefaultOrder("a.");
            ls.add(sqlOrder);
        }
        // 最后 将sqlOrder转成sql语句
        List<String> sqlList = new ArrayList<>();
        for(SqlOrder sqlOrder: ls){
            String temp = sqlOrder.getRealSql();
            sqlList.add(temp);
        }
        return SqlOrder.ORDER_BY + String.join(",", sqlList);
    }

    /**
     * 获取高级查询的sql语句
     * @param builder
     * @return
     */
    private String getSuperQuerySql(StringBuilder builder){
        String sql = builder.toString();
        if(sql==null || "".equals(sql)){
            return "";
        }else{
            return CgformUtil.SQL_AND + "("+sql+") ";
        }
    }


    /**
     * 处理高级查询
     * @param builder
     * @param superQueryParams
     * @param matchType
     * @param alias
     * @param tableName
     * @param allFieldList
     * @param isMain
     * @param isFirst
     * @return
     */
    private boolean handleSuperQueryJoinSql(StringBuilder builder, JSONArray superQueryParams, MatchTypeEnum matchType, String alias, String tableName, List<OnlCgformField> allFieldList, boolean isMain, boolean isFirst){
        boolean temp = isFirst;
        if(superQueryParams!=null){
            for (int i = 0; i < superQueryParams.size(); i++) {
                JSONObject json = superQueryParams.getJSONObject(i);
                String field = json.getString("field");
                String[] fieldArray = field.split(",");
                if(fieldArray.length==1){
                    // 主表
                    if(isMain && CgformUtil.hasField(field, allFieldList)){
                        String aliasField = alias + field;
                        CgformUtil.spliceSuperQueryField(builder, aliasField, json, matchType, null, temp);
                        temp = false;
                    }
                }else{
                    // 子表
                    String realField = fieldArray[1];
                    if(tableName.equalsIgnoreCase(fieldArray[0])){
                        if(CgformUtil.hasField(realField, allFieldList)){
                            String aliasField = alias + realField;
                            CgformUtil.spliceSuperQueryField(builder, aliasField, json, matchType, null, temp);
                            temp = false;
                        }
                    }
                }
            }
        }
        return temp;
    }



    /**
     * join查询后 主表数据去重
     * @param records
     * @return
     */
    private List<Map<String,Object>> handleDuplicateData(List<Map<String,Object>> records){
        Map<String, Map<String,Object>> map = new HashMap<>();
        for(Map<String,Object> temp: records){
            String id = temp.get("id").toString();
            if(map.get(id)==null){
                map.put(id, temp);
            }
        }
        return new ArrayList<Map<String,Object>>(map.values());
    }

    /**
     * 判断某个子表 有没有高级查询条件
     * @return
     */
    private boolean hasSuperQueryParam(OnlTable table, JSONArray superQueryParams){
        if(table.isMain()){
            return true;
        }
        String tableName = table.getTableName();
        if(superQueryParams!=null && superQueryParams.size()>0){
            for (int i = 0; i < superQueryParams.size(); i++) {
                JSONObject json = superQueryParams.getJSONObject(i);
                String field = json.getString("field");
                String[] fieldArray = field.split(",");
                if(fieldArray.length==2){
                    if(fieldArray[0]!=null && fieldArray[0].equals(tableName)){
                        return true;
                    }
                }
            }
        }
        return false;
    }

    /**
     * 判断某个子表 有没有查询字段
     * @return
     */
    private boolean hasSelectField(OnlTable table){
        if(table.isMain()){
            return true;
        }
        // 判断一个子表是否需要join： 字段、权限、高级查询,查询条件 4个条件有一个就需要join ，权限没有 暂不考虑
        List<OnlCgformField> selectFieldList = table.getSelectFieldList();
        if(selectFieldList!=null && selectFieldList.size()>0){
            for(OnlCgformField field: selectFieldList){
                String mainTable = field.getMainTable();
                if(mainTable==null || "".equals(mainTable)){
                    // 查询字段不是 外键字段
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 高级查询 构造条件解析器
     * @param table
     * @param array
     * @param matchType
     * @return
     */
    private ConditionHandler handleSingleTableSuperQuery(OnlTable table, JSONArray array, String matchType, ConditionHandler baseHandler){
        String tableName = table.getTableName();
        boolean isMain = table.isMain();
        List<OnlCgformField> allFieldList = table.getAllFieldList();

        List<OnlineFieldConfig> fieldList = new ArrayList<>();
        if(array!=null){
            for (int i = 0; i < array.size(); i++) {
                JSONObject parameter = array.getJSONObject(i);
                String field = parameter.getString("field");
                String[] fieldArray = field.split(",");
                if(fieldArray.length==1){
                    // 主表
                    if(isMain && CgformUtil.hasField(field, allFieldList)){
                        OnlineFieldConfig temp = new OnlineFieldConfig(parameter);
                        fieldList.add(temp);
                    }
                }else{
                    // 子表
                    String realField = fieldArray[1];
                    if(tableName.equalsIgnoreCase(fieldArray[0])){
                        if(CgformUtil.hasField(realField, allFieldList)){
                            OnlineFieldConfig temp = new OnlineFieldConfig(parameter);
                            fieldList.add(temp);
                        }
                    }
                }
            }

            if(fieldList.size()>0){
                String alias = table.getAlias()+".";
                ConditionHandler handler = new ConditionHandler(alias, true, matchType);
                handler.setDuplicateSqlNameRecord(baseHandler.getDuplicateSqlNameRecord());
                handler.setDuplicateParamNameRecord(baseHandler.getDuplicateParamNameRecord());
                handler.getSuperQuerySql(fieldList);
                return handler;
            }
        }
        return null;
    }
}
